Data warehousing with Oracle

With the emergence of data warehousing, Decision Support Systems have evolved to its best. At the core of these warehousing systems lies a good database management system. Database server, used for data warehousing, is responsible to provide robust data management, scalability, high performance query processing and integration with other servers. Oracle being the initiator in warehousing servers, provides a wide range of features for facilitating data warehousing. This paper is designed to review the features of data warehousing – conceptualizing the concept of data warehousing and lastly, features of Oracle servers for implementing a data warehouse. Data Warehouse – A Conceptual Overview Definition of Data Warehouse W.H. Inmon, “father of data warehousing”, defined data warehouse as: A data warehouse is a Subject Oriented, Integrated, Non-volatile, and Time-variant collection of data in support of management’s decisions. With the advancement in the computing technology, the fall in the computer hardware and change in the nature of business – the value of information have raised dramatically. The need of making decisions on the basis of large amount of data, which has the property of diversification along with the hugeness, have raised to a level not comparable to any phase throughout the history of Information Technology. Supplementing was the betterment of server operating systems and the explosion of Internets and Web based applications. The more organized Information database is – the better is the performance of the company. This indispensable requirement to store enormous amount of data lead to the Analytic Systems which in turn gave birth to the idea of Data Warehousing. Data warehousing is about molding data into information, and storing this information based on the subject rather than application. As mentioned by W.H. Inmon, in one of his articles, the data warehouse environment is the foundation of DSS – Decision Support Systems. Going back to the definition of data warehouse, the warehouse is a Subject Oriented, Integrated, Non-volatile, and Time-variant collection of data. Data Warehousing With Oracle Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 www.oracular.com Contact : Muhammad Ahmad Shahzad Oracular, Inc. Figure 1 Subject-Oriented In data warehousing the prime objective of storing data is to facilitate decision process of a company, and within any company data naturally concentrates around subject areas. This leads to the gathering of information around these subjects rather than around the applications or processes. Integrated Though the data in the data warehouses is scattered around different tables, databases or even servers but the data is integrated consistently in the values of variables, naming conventions and physical data definitions. Nonvolatile Being the snapshot of operational data on a given specific time, the data in the data warehouses should not be changed or updated – once its loaded from operational system. As the snapshot shows operational data at some moment of time and one expects data warehouse to reflect accurate values of that time frame. There exist only two operations – the timebased loading of data, accessing the loaded data. Time-variant The value of operational data changes on the basis of time. The time based archival of data from operational systems to data warehouse, makes the value of data, in the data warehouses, being function of time. As data warehouse gives accurate picture of operational data for some given time and the change in the data in warehouse is based on time based change in operational data, data in the data warehouse is called ‘time-variant’. From the operational systems to the requirement of DSS, to designing of data warehousing, to Implement to ongoing support, data warehousing does not use some alien concepts and is more or less based on the typical System Development Life Cycle (SDLC) concept. Data warehouses possess a degree of multi-dimensioning in there nature. The advocates of Relational Modeling say that Multi-dimensioning of data is just another way of representation of data in two dimensional relational models. If we agree to the above rationale then the data warehousing comes in the umbrella of traditional RDBMS application development process. Yet indeed, there are some major differences when building a warehouse, including features like hugeness of data or accessibility or providing dynamic access etc. The most important difference is of course the way data is placed in data warehouses, its more like summarized, referenced, de-normalized representation. In short what ever or how ever we develop a data warehouse it should at least be capable of providing ad hoc complex, statistical, and analytical queries to facilitate decision making process. Data Warehouse Collection of Data Subject Oriented, Integrated, Non-volatile, and Time varian Data Warehousing With Oracle Oracular, Inc. 317 City Center Oshkosh, WI 54901 920.303.0470 www.oracular.com Contact : Muhammad Ahmad Shahzad Oracular, Inc. Architecture of data warehouse As repeatedly mentioned in this paper, the prime concern of providing a separate set of data – the data warehouse, is to facilitate Business Analysts in the process of Decision Making. Essentially data warehousing is the “warehousing” data outside operational systems and this has not significantly changed with the evolution of data warehousing systems. Prime reason of this separation is that the evaluation and analysis, done by analysts, require complex and analytic queries the effect of which is the performance degradation of operational systems. Another important feature is the combination of data from more than one operational system to provide the ability of cross-referencing.